﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint;
using System.Reflection;
using System.Web;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.Administration;

namespace Square.OverView
{
    public class OverViewBaseControl : System.Web.UI.UserControl
    {   
        public string strIdentity = string.Empty;
        public string partitionid = string.Empty;
        /// <summary>
        /// Get EditGetCommentsCount
        /// my profile page All item count
        /// </summary>
        /// <param name="user">UserProfile user</param>        
        /// <returns>Int TotlaCount</returns>
        #region ##EditGetCommentsCount
        public double EditGetCommentsCount(UserProfile user)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();
            DataSet UrlID = new DataSet();
            DataSet ColleagueID = new DataSet();

            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;
            double TotlaCount = 0;

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            //if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            //{
            //    throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            //}
            //if (startIndex < 0)
            //{
            //    throw new ArgumentOutOfRangeException("startIndex");
            //}

            ColleagueID = GetColleagueID(user);
            if (ColleagueID.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ColleagueID.Tables[0].Rows)
                {
                    if (strUrlid != "")
                        strUrlid += ",";
                    strUrlid += GetColleagueUrlID(user, Convert.ToInt64(dr["ColleagueRecordId"].ToString()));
                }
            }
            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                string[] ArrstrUrlid = strUrlid != "" ? strUrlid.Split(',') : null;

                query.AppendLine("SELECT COUNT(*) as Total");
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE (sc.UrlID = '{0}'", GetColleagueUrlID(user, user.RecordId));


                //query.AppendFormat(" or sc.User_RecordID = '{0}'", "3");

                //query.AppendFormat(" and (sc.UrlID = '{0}'", );
                if (ArrstrUrlid != null)
                {
                    foreach (string urlid in ArrstrUrlid)
                    {
                        query.AppendFormat(" or sc.UrlID = '{0}'", urlid);
                    }
                }

                query.AppendLine(")");
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");                

                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

                TotlaCount = Convert.ToInt64(Getuser.Tables[0].Rows[0]["Total"]);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.Add("@partitionID", SqlDbType.UniqueIdentifier).Value = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();
                //command.Parameters.Add("@correlationId", SqlDbType.UniqueIdentifier).Value = ULS.CorrelationGet();
                //command.Parameters.Add("@user_recordID", SqlDbType.BigInt).Value = user.RecordId;
                //if (maximumItemsToReturn > 0)
                //{
                //    command.Parameters.Add("@max", SqlDbType.Int).Value = maximumItemsToReturn + startIndex;
                //}
                //if (fromDate.HasValue)
                //{
                //    command.Parameters.Add("@fromDate", SqlDbType.DateTime).Value = fromDate.Value;
                //}
                //if (toDate.HasValue)
                //{
                //    command.Parameters.Add("@toDate", SqlDbType.DateTime).Value = toDate.Value;
                //}                

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return TotlaCount;
        }
        #endregion


        /// <summary>
        /// Get EditGetComments
        /// use RecoldId
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="maximumItemsToReturn">int maximumItemsToReturn</param>
        /// <returns>DataSet GetComments</returns>
        #region ##EditGetComments
        public DataSet EditGetComments(UserProfile user, int maximumItemsToReturn)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();
            DataSet UrlID = new DataSet();
            DataSet ColleagueID = new DataSet();

            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            {
                throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            }
            //if (startIndex < 0)
            //{
            //    throw new ArgumentOutOfRangeException("startIndex");
            //}

            ColleagueID = GetColleagueID(user);
            if (ColleagueID.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ColleagueID.Tables[0].Rows)
                {
                    if (strUrlid != "")
                        strUrlid += ",";
                    strUrlid += dr["ColleagueRecordId"].ToString();
                }
            }
            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                string[] ArrstrUrlid = strUrlid != "" ? strUrlid.Split(',') : null;

                query.AppendFormat("SELECT TOP ({0}) sc.CommentID,sc.UrlID,sc.User_RecordID, u.Url, sc.LastModifiedTime, sc.Comment, sc.IsHighPriority, sc.Title", maximumItemsToReturn);
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                //query.AppendLine(" INNER JOIN UserProfile_Full as upf (NOLOCK)");
                //query.AppendLine(" on sc.User_RecordID = upf.RecordID");
                //query.AppendLine(" and sc.PartitionID = upf.PartitionID");
                query.AppendFormat(" WHERE (sc.UrlID = '{0}'", GetColleagueUrlID(user, user.RecordId));


                //query.AppendFormat(" or sc.User_RecordID = '{0}'", "3");

                //query.AppendFormat(" and (sc.UrlID = '{0}'", );
                if (ArrstrUrlid != null)
                {
                    foreach (string urlid in ArrstrUrlid)
                    {
                        query.AppendFormat(" or sc.User_RecordID = '{0}'", urlid);
                    }
                }

                query.AppendLine(")");
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);
                query.AppendFormat(" or sc.User_RecordID = '{0}'", user.RecordId);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");
                query.AppendLine(" ORDER BY sc.LastModifiedTime desc");

                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return Getuser;
        }
        #endregion

        /// <summary>
        /// Get MyComments
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="maximumItemsToReturn">int maximumItemsToReturn</param>
        /// <returns>DataSet GetMyComments</returns>
        #region ##GetMyComments
        public DataSet GetMyComments(UserProfile user, int maximumItemsToReturn)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();
            //DataSet UrlID = new DataSet();
            //DataSet ColleagueID = new DataSet();

            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            {
                throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            }
            
            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                string[] ArrstrUrlid = strUrlid != "" ? strUrlid.Split(',') : null;

                query.AppendFormat("SELECT TOP ({0}) sc.CommentID,sc.UrlID,sc.User_RecordID, u.Url, sc.LastModifiedTime, sc.Comment, sc.IsHighPriority, sc.Title", maximumItemsToReturn);
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.User_RecordID = '{0}'",user.RecordId );
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);
                query.AppendLine(" ORDER BY sc.LastModifiedTime desc");

                query.AppendFormat("  SELECT COUNT(*) Total");
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.User_RecordID = '{0}'", user.RecordId);
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);

                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return Getuser;
        }
        #endregion

        /// <summary>
        /// Get ShareComments
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="maximumItemsToReturn">int maximumItemsToReturn</param>
        /// <returns>DataSet GetShareComments</returns>
        #region ##GetShareComments
        public DataSet GetShareComments(UserProfile user, int maximumItemsToReturn)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();
           
            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            if (user == null)
            {
                throw new ArgumentNullException("user");
            }
            if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            {
                throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            }
           
            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                string[] ArrstrUrlid = strUrlid != "" ? strUrlid.Split(',') : null;

                query.AppendFormat("SELECT TOP ({0}) sc.CommentID,sc.UrlID,sc.User_RecordID, u.Url, sc.LastModifiedTime, sc.Comment, sc.IsHighPriority, sc.Title", maximumItemsToReturn);
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.User_RecordID = '{0}'", user.RecordId);
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);
                query.AppendFormat(" and sc.Title = '{0}'", "Share");
                query.AppendLine(" ORDER BY sc.LastModifiedTime desc");

                query.AppendFormat("  SELECT COUNT(*) Total");
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.User_RecordID = '{0}'", user.RecordId);
                query.AppendFormat(" and sc.PartitionID = '{0}'", partitionid);
                query.AppendFormat(" and sc.Title = '{0}'", "Share");

                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return Getuser;
        }
        #endregion

        /// <summary>
        /// Get EditGetUrlComments
        /// use Url
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="maximumItemsToReturn">int maximumItemsToReturn</param>
        /// <returns>DataSet GetComments</returns>
        #region ##EditGetUrlComments
        public DataSet EditGetUrlComments(string Url, int maximumItemsToReturn)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();


            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;
            string Serializedurl = string.Empty;
            string[] ArrSerializedurl = new string[2];

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            {
                throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            }
            //if (startIndex < 0)
            //{
            //    throw new ArgumentOutOfRangeException("startIndex");
            //}

            //AlternateAccessMapping.GetSerializedUrl
            Serializedurl = HttpUtility.UrlDecode(GetEditSerializedUrl(new Uri(Url)).AbsoluteUri);

            strUrlid = GetUrlID(Serializedurl);


            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                //partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                query.AppendFormat("SELECT TOP ({0}) sc.CommentID,sc.UrlID,sc.User_RecordID, u.Url, sc.LastModifiedTime, sc.Comment, sc.IsHighPriority, sc.Title", maximumItemsToReturn);
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.UrlID = '{0}'", strUrlid);
                query.AppendLine(" ORDER BY sc.LastModifiedTime desc");

                query.AppendFormat(" SELECT COUNT(*) Total");
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.UrlID = '{0}'", strUrlid);

                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return Getuser;
        }
        #endregion

        /// <summary>
        /// Get ColleagueID
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <returns>DataSet ColleagueID</returns>
        #region ##GetColleagueID
        public DataSet GetColleagueID(UserProfile user)
        {
            DataSet ColleagueID = new DataSet();
            SqlDataAdapter adapter;
            StringBuilder ColleagueIDquery = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            //string partitionid = string.Empty;
            string pConn = string.Empty;
            string sConn = string.Empty;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }
            //ColleagueID를 가지고 온다
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                ColleagueIDquery.AppendLine("SELECT Id,RecordId,ColleagueRecordId,GroupType,GroupTitle,IsWorkgroup,ItemSecurity,PartitionID");
                ColleagueIDquery.AppendLine(" FROM UserColleagues (NOLOCK)");
                ColleagueIDquery.AppendFormat(" WHERE RecordId = '{0}'", user.RecordId);
                ColleagueIDquery.AppendFormat(" and PartitionID = '{0}'", partitionid);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");

                command.CommandText = ColleagueIDquery.ToString();
                command.Connection = profileConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(ColleagueID);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return ColleagueID;
        }
        #endregion

        /// <summary>
        /// Get ColleagueUrlID
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="recordid">string recordid</param>
        /// <returns>string UrlID</returns>
        #region ##GetColleagueUrlID
        public string GetColleagueUrlID(UserProfile user, long recordid)
        {
            //DataSet ColleagueID = new DataSet();
            DataSet GetUrlID = new DataSet();
            SqlDataAdapter adapter;
            StringBuilder Urlidquery = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            //string partitionid = string.Empty;
            string pConn = string.Empty;
            string sConn = string.Empty;
            string strUrlid = string.Empty;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            //UrlID를 가지고 온다
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                UserProfileManager myUserProfileManager;
                UserProfile myUserProfile;

                //ColleagueID = GetColleagueID(user);
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();

                //foreach (DataRow dr in ColleagueID.Tables[0].Rows)
                //{
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    SPServiceContext context = SPServiceContext.GetContext(site);
                    myUserProfileManager = new UserProfileManager(context);
                    myUserProfile = myUserProfileManager.GetUserProfile(recordid);
                }
                Urlidquery = new StringBuilder();
                Urlidquery.AppendLine("SELECT UrlID");
                Urlidquery.AppendLine(" FROM Urls WITH (NOLOCK,INDEX=IX_Urls_UrlHash)");
                Urlidquery.AppendFormat(" where UrlHash = checksum('{0}')", myUserProfile.PublicUrl.AbsoluteUri);
                Urlidquery.AppendFormat(" and PartitionID = '{0}'", partitionid);
                Urlidquery.AppendFormat(" and Url = '{0}'", myUserProfile.PublicUrl.AbsoluteUri);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");

                command.CommandText = Urlidquery.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(GetUrlID);

                if (GetUrlID.Tables[0].Rows.Count > 0)
                {
                    if (strUrlid != "")
                        strUrlid += ",";

                    strUrlid = GetUrlID.Tables[0].Rows[0][0].ToString();
                }
                //}

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return strUrlid;
        }
        #endregion

        /// <summary>
        /// Get UrlID
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="recordid">string recordid</param>
        /// <returns>string UrlID</returns>
        #region ##GetUrlID
        public string GetUrlID(string Url)
        {
            //DataSet ColleagueID = new DataSet();
            DataSet UrlID = new DataSet();
            SqlDataAdapter adapter;
            StringBuilder Urlidquery = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            //string partitionid = string.Empty;
            string pConn = string.Empty;
            string sConn = string.Empty;
            string strUrlid = string.Empty;
            string Serializedurl = string.Empty;
            string[] ArrSerializedurl = new string[2];

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            //Url Convert
            Serializedurl = HttpUtility.UrlDecode(GetEditSerializedUrl(new Uri(Url)).AbsoluteUri);

            if (Serializedurl.Contains("="))
            {
                ArrSerializedurl = Serializedurl.Split('=');
                Serializedurl = ArrSerializedurl[0] + "=" + ArrSerializedurl[1].Replace("\\", "%5C").Replace(".", "%2E").Replace("\\\\", "%5C");
            }

            //UrlID를 가지고 온다
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                Urlidquery = new StringBuilder();
                Urlidquery.AppendLine("SELECT UrlID");
                Urlidquery.AppendLine(" FROM Urls WITH (NOLOCK,INDEX=IX_Urls_UrlHash)");
                Urlidquery.AppendFormat(" where UrlHash = checksum('{0}')", Serializedurl);
                //Urlidquery.AppendFormat(" and PartitionID = '{0}'", partitionid);
                Urlidquery.AppendFormat(" and Url = '{0}'", Serializedurl);
                //query.AppendLine(" and sc.LastModifiedTime >= @fromDate and sc.LastModifiedTime < @toDate");

                command.CommandText = Urlidquery.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(UrlID);

                if (UrlID.Tables[0].Rows.Count > 0)
                {
                    strUrlid = UrlID.Tables[0].Rows[0][0].ToString();
                }
            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return strUrlid;
        }

        public void DBCommentDelete(UserProfile MyProfile,string CommentID)
        {
            //SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            //string partitionid = string.Empty;
            string pConn = string.Empty;
            string sConn = string.Empty;
            string strUrlid = string.Empty;

            //if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            //{
            //    pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            //}
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                //profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                //profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                //if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            partitionid = MyProfile.Suggestions.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(MyProfile.Suggestions, null).ToString();

            using (SqlCommand command = new SqlCommand("dbo.proc_SocialComments_Delete",socialConn))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@partitionID", SqlDbType.UniqueIdentifier).Value = new Guid(partitionid);
                //command.Parameters.Add("@correlationId", SqlDbType.UniqueIdentifier).Value = ULS.CorrelationGet();
                command.Parameters.Add("@commentID", SqlDbType.BigInt).Value = CommentID;
                command.Parameters.Add("@user_recordID", SqlDbType.BigInt).Value = MyProfile.RecordId;                
                command.ExecuteNonQuery();        
            }
            //profileConn.Close();
            //if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();            
        }

        protected Uri GetEditSerializedUrl(Uri url)
        {
            if (SPFarm.Local.AlternateUrlCollections.SerializeUrlForStorage(url.AbsoluteUri) != null)
            {
                return new Uri(SPFarm.Local.AlternateUrlCollections.SerializeUrlForStorage(url.AbsoluteUri));
            }

            return url;
        }

        #endregion

        /// <summary>
        /// Get NewCommentNum
        /// use RecoldId
        /// </summary>
        /// <param name="user">UserProfile user</param>
        /// <param name="maximumItemsToReturn">int maximumItemsToReturn</param>
        /// <returns>DataSet GetComments</returns>
        #region ##NewCommentNum
        public DataSet NewCommentNum(UserProfile user)//, DateTime? fromDate, DateTime? toDate)
        {
            DataSet Getuser = new DataSet();


            string pConn = string.Empty;
            string sConn = string.Empty;
            //string partitionid = string.Empty;
            string strUrlid = string.Empty;
            string Serializedurl = string.Empty;
            string[] ArrSerializedurl = new string[2];

            StringBuilder query = new StringBuilder();

            SqlConnection profileConn = new SqlConnection();
            SqlConnection socialConn = new SqlConnection();
            SqlDataAdapter adapter;

            if (System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"] != null)
            {
                pConn = System.Configuration.ConfigurationManager.ConnectionStrings["ProfileConn"].ToString();
            }
            if (System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"] != null)
            {
                sConn = System.Configuration.ConfigurationManager.ConnectionStrings["SocialConn"].ToString();
            }

            try
            {
                profileConn.ConnectionString = pConn;
                socialConn.ConnectionString = sConn;
                profileConn.Open();
                socialConn.Open();
            }
            catch (Exception ex)
            {
                if (profileConn != null) profileConn.Dispose();
                if (socialConn != null) socialConn.Dispose();
                throw new Exception(ex.Message);
            }

            //if ((maximumItemsToReturn < 0) || (maximumItemsToReturn > 0x3e8))
            //{
            //    throw new ArgumentOutOfRangeException("maximumItemsToReturn");
            //}
            //if (startIndex < 0)
            //{
            //    throw new ArgumentOutOfRangeException("startIndex");
            //}

            //AlternateAccessMapping.GetSerializedUrl
            //Serializedurl = HttpUtility.UrlDecode(GetEditSerializedUrl(user.);

            strUrlid = GetUrlID(user.PublicUrl.AbsoluteUri);


            //Get Comment
            using (SqlCommand command = new SqlCommand())//new SqlCommand("dbo.proc_SocialComments_GetForUser"))
            {
                partitionid = user.GetType().GetProperty("PartitionID", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(user, null).ToString();
                
                query.AppendFormat(" SELECT COUNT(*) Total");
                query.AppendLine(" FROM SocialComments as sc (NOLOCK)");
                query.AppendLine(" INNER JOIN Urls as u (NOLOCK)");
                query.AppendLine(" on sc.UrlID = u.UrlID");
                query.AppendLine(" and sc.PartitionID = u.PartitionID");
                query.AppendFormat(" WHERE sc.UrlID = '{0}'", strUrlid);
                query.AppendFormat(" and sc.PartitionID = '{0}'",partitionid);
                query.AppendFormat(" and convert(char(11),sc.LastModifiedTime,121) = '{0}'",DateTime.Now.ToShortDateString());
                command.CommandText = query.ToString();
                command.Connection = socialConn;
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = command;
                adapter.Fill(Getuser);

            }
            profileConn.Close();
            if (profileConn != null) profileConn.Dispose();
            socialConn.Close();
            if (socialConn != null) socialConn.Dispose();

            return Getuser;
        }
        #endregion

        #region ##Properties
        /// <summary>
        /// 접속한 사용자ID
        /// </summary>
        public string CurrentUserId
        {
            set
            {
                strIdentity = value;
            }
            get
            {
                //string strIdentity = HttpContext.Current.User.Identity.Name;
                string[] strNames = strIdentity.Split('\\');
                if (strNames.Length > 0)
                {
                    return strNames[1].ToLower();
                }
                else
                {

                    return strIdentity.ToLower();
                }
            }
        }

        protected string HostURI
        {
            get
            {
                return Context.Request.Url.Host;
            }
        }

        protected string AppURI
        {
            get
            {
                return Context.Request.ApplicationPath;
            }
        }

        protected string RootURI
        {
            get
            {
                return Context.Request.Url.Scheme
                    + "://" + HostURI;
                //+ ":" + Context.Request.Url.Port;
            }
        }

        protected string BaseURI
        {
            get
            {
                return RootURI + AppURI;
            }
        }
        #endregion

        public int SearchFolder(SPList list,string recordid)
        {
            using (SPSite _site = new SPSite(SPContext.Current.Site.ID))
            {
                using (SPWeb _web = _site.OpenWeb())
                {
                    SPList _list = _web.Lists["MemoReplyList"];                    

                    SPQuery query = new SPQuery();
                    StringBuilder strQuery = new StringBuilder();
                    strQuery.AppendLine("<Where>");
                    strQuery.AppendLine("<Eq>");
                    strQuery.AppendLine("<FieldRef Name=\"Title\" />");
                    strQuery.AppendFormat("<Value Type=\"Text\" >{0}</Value>", recordid);
                    strQuery.AppendLine("</Eq>");
                    strQuery.AppendLine("</Where>");

                    //strQuery.AppendLine("<Where>");                    
                    //strQuery.AppendLine("<Eq>");
                    //strQuery.AppendLine("<FieldRef Name=\"RecordID\" />");
                    //strQuery.AppendFormat("<Value Type=\"Text\" >{0}</Value>", recordid);
                    //strQuery.AppendLine("</Eq>");                   
                    //strQuery.AppendLine("</Where>");

                    query.Query = strQuery.ToString();
                    query.RowLimit = Convert.ToUInt32(_list.ItemCount);

                    SPListItemCollection Items = _list.GetItems(query);
                    return Items.Count;                                       
                }
            }
        }

        #region NonHTML
        public string Strip(string HTMLText)
        {
            string NonHTML = string.Empty;
            string temp = System.Text.RegularExpressions.Regex.Replace(HTMLText, @"&nbsp;", string.Empty);
            string temp_1 = System.Text.RegularExpressions.Regex.Replace(temp, @"<(.|\n)*?>", string.Empty);
            string temp_2 = System.Text.RegularExpressions.Regex.Replace(temp_1, @"\n", string.Empty);
            string temp_final = System.Text.RegularExpressions.Regex.Replace(temp_2, @"\r", string.Empty);

            NonHTML = ShortString(temp_final);

            return NonHTML;
        }

        public string ShortString(string str)
        {
            //  str = str.Trim();
            char c;
            string _return;
            int length = 340;
            for (int i = 0; i < length; i++)
            {
                if (i < str.Length)
                {
                    c = str[i];
                    if (char.GetUnicodeCategory(c).ToString() == "OtherLetter") //c가 영어가 아니라면(한글이라면)
                    {
                        length -= 1;
                    }
                }
                else
                {
                    break;
                }
            }
            if (str.Length <= length)
            {
                _return = str;
            }
            else
            {
                str = str.Substring(0, length) + "..."; ;
                _return = str;
            }
            return _return;
        }
        #endregion
    }
}
